A. Field of the Invention
The present invention is directed toward the field of information systems. More particularly, the present invention is directed toward the multi-dimensional organization, generation, maintenance, and viewing of two-pass values.
B. Description of Related Art
When analyzing information it is often desirable to scrutinize data that is derived from the information being analyzed. For example, when analyzing sales revenues contributed by a number of divisions in a corporation, it is useful to know what percentage of the corporation's total sales revenue is contributed by each division. Such a percentage value is referred to as a two-pass value, while the underlying sales revenue figures are referred to as one-pass values.
A one-pass value is a measure result that is obtained by performing some type of processing operation on a set of data records. A two-pass value is a measure result that is derived from processing a set of one-pass values. Examples of two-pass values include relative percentages, relative rankings, and running totals of one-pass values.
A raw data record contains values that are classified as being either a measure value or a dimension value. The dimension values characterize the measure values, and the measure values contain data to be either quantitatively or qualitatively analyzed.
A multi-dimensional view provides an environment in which a set of data records can be analyzed. Such analysis is made possible by converting the data records' measure values into one-pass value measure results and two-pass value measure results. The measure results are then displayed, so that they are characterized by the data records' dimension values.
For example, a company may have sold video cassette recorders ("VCR"), televisions ("TV"), and stereos in 1995 and 1996 in both an Eastern region and a Western region of the United States. The sales revenue measure values representing the VCR, TV, and stereo sales may be characterized by a number of different dimensions. One possible set of dimensions includes a region dimension, year dimension, and product dimension.
FIG. 1(a) illustrates a multi-dimensional view 100 characterizing the company's sales with respect to the region, year, and product dimensions. The multi-dimensional view 100 is formed so that the cells 101 are filled with sales revenue measure results, which are obtained from a set of data records. Each axis 103, 104 in the view 100 is divided into sections that represent a set of dimension values. Each section on the horizontal axis 104 corresponds to a unique pair of a year dimension value and a product dimension value. Each section on the vertical axis 103 corresponds to a unique region dimension value.
Each cell 101 in the view 100 contains a sales revenue measure result indicating the sales revenue of a product in a particular year in a particular region. For example, the upper lefthand cell in the view 100 contains a measure result indicating that there were $30,000 of VCR sales in the Eastern region of the United States in 1995. The sales revenue measure results appearing in the cells 101 in FIG. 1(a) are one-pass values, because they have been directly derived by processing measure values in a set of data records.
FIG. 1(b) illustrates an alternate multi-dimensional view 110 in which the cells 111 are filled with percentages, which are two-pass value measure results. The sections of the horizontal axis 114 and vertical axis 113 in the view 110 in FIG. 1(b) are the same as for the view 100 in FIG. 1(a). The only difference is that the measure results in the cells 111 contain the percentage of sales revenue that is contributed by each product for a combination of a region and a year.
For example, the upper lefthand cell in the FIG. 1(b) view 110 indicates the percentage of Eastern sales revenue in 1995 that was attributable to VCR sales. This two-pass value measure result is 30%. The measure results in FIG. 1(b) are two-pass values, because they are calculated based on the one-pass value measure results in the view shown in FIG. 1(b).
The data records that are used as the ultimate basis for generating a multi-dimensional view are retrieved from a database or other source of records, such as a data file. Database management systems are employed to manage such databases. These systems provide for storing, accessing, and manipulating data records. Records can be extracted from a database management system by submitting a query to the system. In response to the query, the database management system searches the records in the database to identify and provide a set of records which correspond to the requirements set forth in the query.
One traditional method of preparing multi-dimensional views having two-pass value measure results is by using SQL statements. Through the use of SQL statements, a record management system submits queries to a database management system that will result in the receipt of data records containing two-pass value measure results. Such SQL statements request data records that include specified dimension values and measure values. These SQL statements also call for one-pass values to be derived from the requested data records and two-pass value measure results to be derived from the one-pass values. As a result, the two-pass value measure results for use in a multi-dimensional view are provided in response to the SQL statement query.
However, the computation time and memory resources required for calculating the two-pass value measure results using SQL statements is excessive. In order to account for many different possible multi-dimensional views, a great number of different two-pass value measure results are calculated. Such two-pass value measure results may include ranks and percentages for different multi-dimensional views that could possibly be formed from the retrieved data records. By calculating the two-pass value measure results for many possible views, some of the two-pass value measure results that are calculated will never be employed. Consequently, the time spent in calculating these measure results and the many resources used to store them were wasted.
Further, the use of SQL statements limits the number of possible multi-dimensional views of two-pass values that can be created from a set of retrieved data records. Since two-pass value measure results are all performed simultaneously with a query, no new two-pass value measure results can be obtained once the query in completed. For newly desired two-pass value measure results, an entirely new query must be performed, even if the information in the retrieved data records is sufficient for deriving the two-pass value measure results. This is undesirable, since the new query will cause delay and utilize additional memory resources. Such delay is unacceptable when operating in an on-line analytical processing (OLAP) environment.
Another traditional method for preparing multi-dimensional views having two-pass value measure results is to create a multi-dimensional record structure containing two-pass value measure results. However, this method suffers drawbacks that are similar to the SQL statement method described above.
FIG. 2 illustrates a traditional multi-dimensional record structure 90 characterizing the previously mentioned company's sales revenue percentages with respect to the region, year, and product dimensions. The record structure 90 is formed so that cells 91.sub.1-12 in the structure 90 are filled with sales revenue percentage measure results. The x-axis 92 of the structure 90 is sectioned into regions that correspond to a set of product dimension values (VCRs, TVs, and stereos). The y-axis 93 of the structure 90 is sectioned into regions that correspond to a set of region dimension values (East and West). The z-axis 94 of the structure 90 is sectioned into regions that correspond to a set of year dimension values (1995 and 1996).
Each sales revenue percentage in a cell 91.sub.1-12 is characterized by a combination of a product dimension value, region dimension value, and year dimension value. For example, the percentage in the front upper lef thand cell 91.sub.1 is characterized by the VCR product dimension value, East region dimension value, and 1995 year dimension value. Accordingly, the percentage measure result in cell 91.sub.1 indicates the percentage of East sales revenue in 1995 attributable to VCR sales.
In order to obtain a two-pass value measure result using the record structure method, a database query is performed and the resulting data records are used as a basis for calculating one-pass values and deriving two-pass value measure results from the one-pass values. Multi-dimensional views are then created by taking slices of the record structure 90 along planes in the record structure 90 that are perpendicular to a record structure axis 93, 94, and 92.
However, an entirely new record structure will have to be created, if a desired view is not within the limited number of available views in the existing structure or the two-pass value measure result to be viewed is not included in the existing structure. As a result, significant amount of time and memory resources are required to create a new structure, since the existing record structure cannot be augmented. Additionally, constructing a record structure containing all possible views and two-pass value measure results causes computer and memory resources to be unnecessarily spent on generating views that may never be displayed.
Accordingly, it is desirable to provide for the efficient use of both time and memory resources in the generation of multi-dimensional views containing two-pass value measure results. It is therefore desirable to provide for generating all possible two-pass value multi-dimensional views that are supported by a set of retrieved data records. It is also desirable for a two-pass value measure result to only be calculated when there is an actual request for a multi-dimensional view containing such a two-pass value measure result.